from .guidancesql import *


def selintention(serviceid, cmpid, dbname, cid):
    if serviceid == "":
        sql = "select e.expressionid,e.expression,m.intentionid from zt_intention_main m ,zt_intention_expression e " \
              "where m.intentionid=e.intentionid and m.cmpid=" + str(cmpid) + " "  # and e.state=1
    else:
        sql = "select e.expressionid,e.expression,m.intentionid  " \
              "FROM zt_intention_main_band b,zt_intention_main m ,zt_intention_expression e  " \
              "where m.intentionid=e.intentionid  and m.intentionid=b.mainid and b.serviceid=" + str(serviceid) + " ;"
    allintention = conn.mysql_sel(sql, dbname, cid)

    return allintention


def expression_dictionaries(expressionid, grooveid, keymainid, dbname, cid):
    sql = "SELECT g.dictionariesid,g.grooveid from zt_intention_groove g ,zt_intention_key k" \
          " where g.grooveid=k.grooveid and k.state=2  and k.keymainid='" + str(keymainid) + "' "
    groovedata = conn.mysql_sel(sql, dbname, cid)
    return groovedata


def expression_dictionariesagant(expressionid, grooveid, keymainid, dbname, cid):
    sql = "SELECT g.dictionariesid,g.grooveid from zt_intention_groove g ,zt_intention_key k" \
          " where g.grooveid=k.grooveid  and g.expressionid='" + str(expressionid) + "' and k.keymainid='" + str(
        keymainid) + "' "
    groovedata = conn.mysql_sel(sql, dbname, cid)
    return groovedata


def insertintentionkey(cid, grooveid, intentionkey, keymainid, dbname):
    sql = " UPDATE zt_intention_key set state=1 ,intentionkey='" + str(intentionkey) + "' " \
                                                                                       "where grooveid='" + str(
        grooveid) + "' and cid='" + str(cid) + "' and keymainid= '" + str(keymainid) + "'"

    updataid = conn.mysql_updata(sql, dbname, cid)
    return updataid


def selintentionkeyword(dictionariesid, dbname, cid):
    sql = " SELECT  GROUP_CONCAT(w.word SEPARATOR '|'),w.type,d.dictionariesid from zt_intention_dictionaries d" \
          " left join zt_intention_dictionaries_word w on w.dictionariesid=d.dictionariesid " \
          " where   d.dictionariesid='" + str(dictionariesid) + "'  " \
                                                                "  GROUP BY w.type,d.dictionariesid "
    keyword = conn.mysql_sel(sql, dbname, cid)  # d.state=1 and w.state=1 and

    return keyword


def mustgroove(grooveid, dbname, cid):
    sql = "select g.required,g.feedbackid from zt_intention_groove g where g.grooveid='" + str(grooveid) + "' "
    groovedata = conn.mysql_sel(sql, dbname, cid)
    return groovedata


def feedbackdata(feedbackid, dbname, cid):
    sql = "SELECT f.feedback,f.type,f.remark,f.qtype from zt_intention_feedback f where f.feedbackid='" + str(
        feedbackid) + "'"
    feeddata = conn.mysql_sel(sql, dbname, cid)
    return feeddata


def seldgroove(expressionid, dbname, cid):
    sql = "select GROUP_CONCAT(w.word),w.type,d.dictionariesid,g.required,g.feedbackid " \
          "from zt_intention_groove g LEFT   JOIN zt_intention_dictionaries d on g.dictionariesid=d.dictionariesid " \
          " LEFT JOIN zt_intention_dictionaries_word w on w.dictionariesid=d.dictionariesid " \
          " where  g.expressionid='" + str(expressionid) + "'  GROUP BY w.type,g.grooveid "
    groovedata = conn.mysql_sel(sql, dbname, cid)  # g.state=1 and d.state=1 and w.state=1  and
    return groovedata


def insalldata(cid, expressionid, intentionid, keymainid, dbname):
    sql = " select  g.grooveid from zt_intention_groove g where g.intentionid = '" + str(
        intentionid) + "' ORDER BY g.sort "  # and g.state=1
    data = conn.mysql_sel(sql, dbname, cid)
    if data != None and len(data) != 0:

        insql = ''
        for grooveid in data:
            insql = "INSERT into zt_intention_key(cid,grooveid,keymainid) VALUES ('" + str(cid) + "','" + str(
                grooveid[0]) + "','" + str(keymainid) + "');"
            insertdata = conn.mysql_insert(insql, dbname, cid)

        return data[0][0]


def selwordgrooveid(gvalue, intentionid, dbname, cid):
    sql = "select g.grooveid from zt_intention_groove g  where g.gvalue='" + str(
        gvalue) + "' and g.intentionid='" + str(intentionid) + "' LIMIT 0,1"
    grooveid = conn.mysql_selrowid(sql, dbname, cid)
    return grooveid


def selgroove(cid, keymainid, dbname):
    sql = "select k.grooveid from zt_intention_key k,zt_intention_groove g where k.keymainid='" + str(keymainid) + "'" \
                                                                                                                   " and k.state=2 and k.grooveid=g.grooveid and g.required=1  ORDER BY k.intentionkeyid  LIMIT 0,1"
    grooveid = conn.mysql_selrowid(sql, dbname, cid)
    return grooveid


def getnotinputintention(cid, grooveid, keymainid, num, dbname):
    sqlcount = "select count(*) from " \
               "zt_intention_key k,zt_intention_groove g,zt_intention_feedback f " \
               "where g.grooveid=k.grooveid and k.state=2  and k.keymainid='" + str(keymainid) + "' " \
                                                                                                 "and f.grooveid=g.grooveid and k.cid='" + str(
        cid) + "' and g.grooveid = '" + str(grooveid) + "'  ORDER BY g.sort,f.sort  "
    allcount = conn.mysql_selrowid(sqlcount, dbname, cid)
    count = 0
    # print('sssss',num,allcount)
    if str(allcount) != '0':
        count = int(int(num) % int(allcount))
        # print(count)
        if count != 0:
            count = count - 1
        else:
            count = allcount - 1

    sql = "select f.feedback,f.type,f.remark,f.qtype,g.grooveid,'','' from " \
          "zt_intention_key k,zt_intention_groove g,zt_intention_feedback f " \
          "where g.grooveid=k.grooveid and k.state=2  and k.keymainid='" + str(keymainid) + "' " \
                                                                                            "and f.grooveid=g.grooveid and k.cid='" + str(
        cid) + "'  and g.grooveid = '" + str(grooveid) + "' ORDER BY g.sort,f.sort    LIMIT " + str(count) + ",1"
    data = conn.mysql_sel(sql, dbname, cid)

    return data


def finallyintention(expressionid, dbname, cid):
    sql = "select m.endwords,m.type,m.remark,m.qtype,m.url,m.stype,m.isend from zt_intention_main m ,zt_intention_expression e " \
          " where m.intentionid=e.intentionid and e.expressionid='" + str(expressionid) + "'"

    data = conn.mysql_sel(sql, dbname, cid)
    return data


def getintentionurlkey(keymainid, cid, dbname):
    sql = "select GROUP_CONCAT(CONCAT(g.urlkey,'=',IFNULL(k.intentionkey,'')) SEPARATOR '&')" \
          " from zt_intention_key k , zt_intention_groove g where  g.grooveid=k.grooveid" \
          " AND k.keymainid='" + str(keymainid) + "' and k.cid='" + str(cid) + "'" \
                                                                               "  and g.urlkey is not null and g.urlkey!=''"
    data = conn.mysql_selrowid(sql, dbname, cid)
    if data == None:
        data = ''
    return data


# ��ѯ�ض��ӿڵ�
def sel_key_data(cid, dbname):
    sql = "SELECT k.intentionkey from zt_intention_key k  where k.cid='" + str(cid) + "' ;"
    data = conn.mysql_selrowid(sql, dbname, cid)
    return data


def selgrooveidci(grooveid, keymainid, dbname, cid):
    sql = "select GROUP_CONCAT(w.word SEPARATOR '|'),w.type,d.dictionariesid,g.grooveid from " \
          "zt_intention_key k ,zt_intention_groove g,zt_intention_dictionaries d," \
          "zt_intention_dictionaries_word w where k.grooveid=g.grooveid " \
          "and g.dictionariesid=d.dictionariesid and d.dictionariesid=w.dictionariesid " \
          "and g.grooveid='" + str(grooveid) + "' and k.keymainid='" + str(
        keymainid) + "' GROUP BY w.type,d.dictionariesid"
    data = conn.mysql_sel(sql, dbname, cid)

    return data


@time_statistic(3,'cid')
def selbackfeeckcycle(grooveid, num, dbname, cid):
    sqlcount = " select count(*) from zt_intention_feedback f  where f.grooveid='" + str(grooveid) + "'"

    allcount = conn.mysql_selrowid(sqlcount, dbname, cid)

    count = 0
    if str(allcount) != '0':
        count = int(int(num) % int(allcount))
        if count != 0:
            count = count - 1
        else:
            count = allcount - 1

    sql = " select f.feedback,f.type,f.remark,f.qtype,'','','' from zt_intention_feedback f  " \
          " where f.grooveid='" + str(grooveid) + "' ORDER BY f.sort  LIMIT " + str(count) + ",1  "

    feedbackdata = conn.mysql_sel(sql, dbname, cid)

    return feedbackdata


def selintentionkey(grooveid, cid, keymainid, dbname):
    sql = "select k.intentionkey from zt_intention_key k where k.cid='" + str(cid) + "' and k.grooveid='" + str(
        grooveid) + "' and k.keymainid='" + str(keymainid) + "'"
    data = conn.mysql_selrowid(sql, dbname, cid)
    return data


def install_key_main(cid, expressionid, dbname):
    sql = "insert into zt_intention_key_main(cid,expressionid) VALUES ('" + str(cid) + "','" + str(expressionid) + "')"
    insertid = conn.mysql_insertid(sql, dbname, cid)
    return insertid


def updataKeymain(keymainid, dbname, cid):
    sql = "UPDATE zt_intention_key_main set state=1 where keymainid= '" + str(keymainid) + "'"
    updata = conn.mysql_updata(sql, dbname, cid)
    return updata


def selexpretion(intentionid, dbname, cid):
    sql = "select e.expressionid from zt_intention_expression e where e.intentionid='" + str(
        intentionid) + "' LIMIT 0,1"
    expretionid = conn.mysql_selrowid(sql, dbname, cid)
    return expretionid


def selkeyvalue(intentionid, dbname, cid):
    sql = "select g.keyword from zt_intention_groove g where g.intentionid='" + str(
        intentionid) + "' and g.keyword is not null;"
    data = conn.mysql_sel(sql, dbname, cid)

    return data
